#encoding:utf-8
import os
import sys
import json
import xlrd
import zipfile
from xlrd.xlsx import X12Book, open_workbook_2007_xml
from mysql import connector as db
import xmltodict as xd
from collections import OrderedDict
from time import time
import logging
logging.basicConfig(level=logging.INFO, format='%(msg)s')

DB_CONF = dict(user='root', password='root',
               host='172.16.16.32', database='goods')


class mysql(object):
    def __init__(self, user, password, host, database):
        try:
            logging.debug('Connecting mysql database...')
            self.conn = db.connect(
                user=user, password=password, host=host, port=3306,
                buffered=True, database=database)
            self.cursor = self.conn.cursor(dictionary=True)
            logging.info('Connect database ok')
        except Exception as e:
            logging.error('Connect database error')
            logging.exception(e)

    def gets(self, tableName, colums=None,
             condition=None, orders='', limits=''):
        colums = colums or '*'
        condition = condition or ''
        if isinstance(condition, dict):
            _keys = condition.keys()
            _data = condition.values()
            condition = ''
        else:
            _keys = []
            _data = []
        sql = "SELECT " + colums + " FROM " + tableName + " WHERE 1=1"
        if _keys:
            for i in _keys:
                sql = sql + " AND " + i + "=%s"
        else:
            sql = sql + condition
        if orders != '':
            sql = sql + ' order by ' + orders
        if limits != '':
            sql = sql + ' limit ' + limits
        self.cursor.execute(sql, _data)
        return self.cursor.fetchall()

    def get(self, tableName, colums=None,
            condition=None, orders='', limits=''):
        colums = colums or '*'
        condition = condition or ''
        if isinstance(condition, dict):
            _keys = condition.keys()
            _data = condition.values()
            condition = ''
        else:
            _keys = []
            _data = []
        sql = "SELECT " + colums + " FROM " + tableName + " WHERE 1=1"
        if _keys:
            for i in _keys:
                sql = sql + " AND " + i + "=%s"
        else:
            sql = sql + condition
        if orders != '':
            sql = sql + ' order by ' + orders
        if limits != '':
            sql = sql + ' limit ' + limits
        self.cursor.execute(sql, _data)
        return self.cursor.fetchone()

    def insert(self, tableName, data):
        sql = "INSERT INTO " + tableName + self.format_data(data, 'insert')
        logging.debug(sql)
        self.cursor.execute(sql, data.values())
        _id = self.cursor.lastrowid
        self.conn.commit()
        return _id

    def delete(self, tableName, condition):
        sql = "DELETE FROM " + tableName + " WHERE 1=1"
        if type(condition) == dict:
            sql += ' AND '
            sql += self.format_data(condition, 'condition')
            _data = condition.values()
        else:
            sql += ' AND '
            sql = sql + condition
            _data = None
        logging.debug(sql)
        self.cursor.execute(sql, _data)
        self.conn.commit()

    def update(self, tableName, data, condition):
        sql = "UPDATE " + tableName + " SET "
        sql += self.format_data(data, 'update')
        sql += " WHERE 1=1 "
        if type(condition) == dict:
            sql += ' AND '
            sql += self.format_data(condition, 'condition')
            _data = data.values() + condition.values()
        else:
            sql += 'AND '
            sql = sql + condition
            _data = data.values()

        logging.debug(sql)
        logging.debug(_data)
        self.cursor.execute(sql, _data)
        self.conn.commit()

    def execute(self, sql, data=None, auto_commit=True):
        try:
            self.cursor.execute(sql)
            if auto_commit:
                self.conn.commit()
        except db.Error as e:
            logging.exception(e)
        finally:
            self.cursor.close()
            self.conn.close()

    def format_data(self, data, _type):
        sql = ''
        fn = lambda x, r: r + r'%s'# if isinstance(x, float) else r + '%s'
        if _type == 'insert':
            fm = ", ".join([fn(v, '') for v in data.values()])
            sql += '('
            sql += ','.join(data.keys())
            sql += ") VALUES("
            sql += fm
            sql += ')'
        elif _type == 'update':
            sql = ', '.join([k + fn(v, '=') for k, v in data.items()])
        elif _type == 'condition':
            sql = ' AND '.join([k + fn(v, '=') for k, v in data.items()])
        return sql


class Excel:
    """docs"""
    def __init__(self, xlsx_file, *args, **kwargs):
        self.zf = None
        self.component_names = {}
        # self.bk = Excel.workbook(xls_file, *args, **kwargs)
        self._open_file(xlsx_file)
        self.bk = open_workbook_2007_xml(self.zf, self.component_names)
        self._sheet = self.get_sheet()
        # self._images = self.get_images()

    def _open_file(self, filename):
        peeksz = 4
        with open(filename, "rb") as f:
            peek = f.read(peeksz)
        if peek == b"PK\x03\x04":   # a ZIP file
            self.zf = zipfile.ZipFile(filename)
            self.component_names = dict([(X12Book.convert_filename(name), name)
                                        for name in self.zf.namelist()])

    @staticmethod
    def workbook(xls_file, *args, **kwargs):
        try:
            return xlrd.open_workbook(xls_file, *args, **kwargs)
        except Exception as e:
            print e.message
            sys.exit()

    @property
    def book(self):
        return self.bk

    @property
    def data(self):
        return self._sheet

    def show_sheets(self):
        return self.bk.sheet_names()

    def get_sheet(self, name=0):
        if isinstance(name, int):
            return self.bk.sheet_by_index(name)
        if isinstance(name, str):
            return self.bk.sheet_by_name(name)
        else:
            print 'No sheet got'
            return None

    def get_row_data(self, rowx, sh=None, colrange=None, block=10):
        result = []
        sh = sh or self._sheet
        dmode = self.bk.datemode
        ctys = sh.row_types(rowx)
        cvals = sh.row_values(rowx)
        if not colrange:
            colrange = xrange(sh.row_len(rowx))
        for colx in colrange:
            cty = ctys[colx]
            cval = cvals[colx]
            if self.bk.formatting_info:
                cxfx = str(sh.cell_xf_index(rowx, colx))
            else:
                cxfx = ''
            if cty == xlrd.XL_CELL_DATE:
                try:
                    showval = xlrd.xldate_as_tuple(cval, dmode)
                except xlrd.XLDateError as e:
                    showval = "%s:%s" % (type(e).__name__, e)
                    cty = xlrd.XL_CELL_ERROR
            elif cty == xlrd.XL_CELL_ERROR:
                showval = xlrd.error_text_from_code.get(cval, '<Unknown error code 0x%02x>' % cval)
            else:
                showval = cval
            result.append((colx, cty, showval, cxfx))
        return result

    def get_sheet_data(self, sh=None):
        data = {}
        sh = sh or self._sheet
        rows, cols = sh.nrows, sh.ncols
        # colrange = range(cols)
        keys = [val.replace(' ', '') for colx, ty, val, cxfx in self.get_row_data(0, sh)]

        data['SheetName'] = sh.name
        data['Keys'] = keys
        data['Data'] = []
        for rowx in xrange(1, rows):
            values = [val.replace(' ', '') for colx, ty, val, cxfx in self.get_row_data(
                rowx, sh)]
 
            data['Data'].append(values)
        return data

    def row_iter(self, sh=None, start=1):
        sh = sh or self._sheet
        strip_sp = lambda x: x.replace(' ', '') if isinstance(x, (str, unicode)) else x
        for rowx in xrange(start, sh.nrows):
            row = [strip_sp(val) for colx, ty, val, cxfx in self.get_row_data(rowx, sh)]
            # logging.debug('Get excel row %d data:' % rowx)
            # logging.debug(row)
            yield row

    def get_images(self, save_path, shidx=None):
        if not os.path.exists(save_path):
            os.makedirs(save_path)
        shidx = shidx or 1
        ret = OrderedDict()
        xml1 = self.zf.open(
            self.component_names['xl/drawings/drawing%d.xml' % shidx])
        xml2 = self.zf.open(
            self.component_names['xl/drawings/_rels/drawing%d.xml.rels' % shidx])
        r1 = xd.parse(xml1)
        r2 = xd.parse(xml2)
        rows = r1['xdr:wsDr']['xdr:twoCellAnchor']
        for r in rows:
            rid = r['xdr:from']['xdr:row']
            cid = r['xdr:from']['xdr:col']
            pic = r['xdr:pic']['xdr:blipFill']['a:blip']['@r:embed']
            ret[rid] = OrderedDict(col=cid, pic=pic)
        rows = r2['Relationships']['Relationship']
        tmp = {}
        for r in rows:
            img_name = r['@Target'].split('/')[-1]
            img_ext = img_name.split('.')[-1]
            img = self.zf.open(
                self.component_names['xl/media/' + img_name]).read()
            sha1 = self.hash_image(img)
            _path = os.path.join(save_path, sha1 + '.' + img_ext)
            self.save_image(img, _path)
            tmp[r['@Id']] = dict(name=img_name, path=_path, hash=sha1)
        for k, v in ret.items():
            v['pic'] = tmp[v['pic']]
        return ret

    def hash_image(self, stream):
        import hashlib
        sha = hashlib.sha1()
        sha.update(stream)
        return sha.hexdigest()

    def save_image(self, stream, path):
        if not os.path.exists(path):
            logging.info('Save image: %s' % path)
            with open(path, 'wb') as f:
                f.write(stream)
        else:
            logging.debug('Exist image: %s' % path)


def parse_goods(
    xlsx, order_id, img_save_path='goods', platform_type=0, skips=None,
    keys=['stock_location', 'case_number', 'img', 'brand', 'category', 'color',
          'size', 'goods_number', 'sku_material_number', 'sku_name',
          'price_offical', 'price_offical_eu', 'price_jd', 'price_supply',
          'discount', 'total', 'jd_pic']):
    db = mysql(**DB_CONF)
    ex = Excel(xlsx)
    rows = ex.row_iter()
    pics = ex.get_images(img_save_path)

    def check_insert(table, con, data, ret_id=None):
        d = db.get(table, condition=con)
        _new = False
        if not d:
            logging.info('Insert %s with data: %s' %  (table, json.dumps(data, ensure_ascii=False)))
            _id = db.insert(table, data)
            d = db.get(table, condition={ret_id: _id})
            _new = True
        else:
            logging.debug('Exist item: %s' % json.dumps(con, ensure_ascii=False))
            _id = d[ret_id] if ret_id else None
        return _id, d, _new
    row_idx = 1
    for row in rows:
        # logging.debug('Parse row %d: %s' % (row_idx, ','.join(row)))
        if len(row) - len(keys) != 0:
            delta = len(row) - len(keys)
            row += [None] * delta
        rd = dict(zip(keys, row))
        logging.debug(json.dumps(rd, ensure_ascii=False))
        bid, _, _ = check_insert(
            'goods_brand',
            {'brand_name': rd['brand']},
            {'brand_name': rd['brand'], 'time': time()},
            'brand_id')
        cid, _, _ = check_insert(
            'goods_category',
            {'category_name': rd['category']},
            {'category_name': rd['category'],
             'parent_id': '-1',
             'create_time': time(),
             'category_mark': 'gom'},
            'category_id')
        color_id, _, _ = check_insert(
            'color',
            {'color_name': rd['color']},
            {'color_name': rd['color']},
            'color_id')

        gid, goods, _new = check_insert(
            'goods_common',
            {'goods_number': rd['goods_number']},
            {'brand_id': bid,
             'category_id': cid,
             'goods_number': rd['goods_number'],
             'price_offical': rd['price_offical'],
             'price_offical_eu': rd['price_offical_eu'],
             'price_jd': rd['price_jd'],
             'price_supply': rd['price_supply'],
             'create_time': time(),
             'update_time': time()},
            'goods_id')
        if not _new:
            _data = {'brand_id': bid,
                 'category_id': cid,
                 'goods_number': rd['goods_number'] or goods['goods_number'],
                 'price_offical': rd['price_offical'] or goods['price_offical'],
                 'price_offical_eu': rd['price_offical_eu'] or goods['price_offical_eu'],
                 'price_jd': rd['price_jd'] or goods['price_jd'],
                 'price_supply': rd['price_supply'] or goods['price_supply'],
                 'update_time': time()}
            logging.info('Update goods(%s) with data: %s' % (gid, json.dumps(_data, ensure_ascii=False)))
            db.update(
                'goods_common',
                _data,
                {'goods_id': gid})

        sku_id, sku, _new = check_insert(
            'sku_common',
            {'sku_material_number': rd['sku_material_number']},
            {'goods_id': gid,
             'sku_material_number': rd['sku_material_number'],
             'sku_source': 1,
             'sku_model': rd['size'],
             'color_id': color_id,
             'create_time': time(),
             'update_time': time()},
            'sku_id')
        if not _new:
            _data = {'sku_name': rd['sku_name'] or sku['sku_name'],
                 'color_id': color_id,
                 'sku_model': rd['size'] or sku['sku_model'],
                 'update_time': time()}
            logging.info('Update sku(%s) with data: %s' % (sku_id, json.dumps(_data, ensure_ascii=False)))
            db.update(
                'sku_common',
                _data,
                {'sku_id': sku_id})

        stock_id, stock, _new = check_insert(
            'sku_stock',
            {'sku_id': sku_id,
             'stock_location': rd['stock_location']},
            {'sku_id': sku_id,
             'stock_location': rd['stock_location'],
             'stock_case_number': rd['case_number'],
             'stock_number': rd['total']},
            'stock_id')
        if not _new:
            _data = {'stock_case_number': rd['case_number'] or stock['stock_case_number'],
                 'stock_number': rd['total'] or stock['stock_number']}
            logging.info('Update sku_stock(%s) with data: %s' % (stock_id, json.dumps(_data, ensure_ascii=False)))
            db.update(
                'sku_stock',
                _data,
                {'stock_id': stock_id})

        db.insert(
            'relation_sku_order',
            {'sku_id': sku_id,
             'order_id': order_id})
        try:
            pic = pics[str(row_idx)]['pic']
            pid, _, _new = check_insert(
                'picture',
                {'file_hash': pic['hash']},
                {'file_hash': pic['hash'],
                 'picture_path': pic['path'],
                 'picture_name': pic['name'],
                 'platform_type': platform_type},
                'picture_id')
        except Exception as e:
            logging.warn('Picture not found in row %d' % row_idx)
            pid = None
        if pid:
            rel_gp, _, _ = check_insert(
                'relation_goods_picture',
                {'goods_id': gid,
                 'picture_id': pid},
                {'goods_id': gid,
                 'picture_id': pid},
                'picture_id')
        row_idx += 1
    return (row_idx - 1)


if __name__ == '__main__':
    try:
        xls, order_id, img_path, platform_type = sys.argv[1:5]  
    except Exception:
        try:
            xls, order_id = sys.argv[1:3]
        except Exception:
            logging.error('e.g.:')
            logging.error('%s xlsx order_id\nor' % sys.argv[0].split(os.path.sep)[-1])
            logging.error('%s xlsx order_id img_save_path platform_type' % sys.argv[0].split(os.path.sep)[-1])
            sys.exit()
    start = time()
    if len(sys.argv) == 3:
        count = parse_goods(xls, order_id)
    else:
        count = parse_goods(xls, order_id, img_path, platform_type)
    used = time() - start
    logging.info('Parse %d goods used %d seconds' % (count, used))
